﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SQLite;
using System.Data.SqlServerCe;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Globalization;
using System.Xml;

// Assembly marked as compliant.
[assembly: CLSCompliantAttribute(true)]

namespace SQLite2CELib
{
    public class Converter
    {
        private DateTime SqlCEMinDateTime = new DateTime(1753, 1, 1, 12, 0, 0, 0);

        // parameters
        private bool _wantVerboseReport;
        private bool _forceOverwrite;
        private string _liteDBPath;
        private string _ceDBPath;
        private string _reportPath;
        private string _settingsPath;
        private bool _skipViews;
        private bool _skipTriggers;
        private bool _skipData;

        private StringBuilder _sbReport = new StringBuilder();
        private StringBuilder _sbLiteSchemaCommands = new StringBuilder();
        private StringBuilder _sbCESchemaCommands = new StringBuilder();
        private StringBuilder _sbCEDataCommands = new StringBuilder();

        private static ConverterSettings _settings = new ConverterSettings();

        public void Init(bool wantVerboseReport, bool forceOverwrite, string liteDBPath, string ceDBPath, string reportPath, string settingsPath,
            bool skipViews, bool skipTriggers, bool skipData)
        {
            _wantVerboseReport = wantVerboseReport;
            _forceOverwrite = forceOverwrite;
            _liteDBPath = liteDBPath;
            _ceDBPath = ceDBPath;
            _reportPath = reportPath;
            _settingsPath = settingsPath;
            _skipViews = skipViews;
            _skipTriggers = skipTriggers;
            _skipData = skipData;

            _sbReport.Clear();
            _sbLiteSchemaCommands.Clear();
            _sbCESchemaCommands.Clear();
            _sbCEDataCommands.Clear();
        }

        public void Convert()
        {
            Console.WriteLine("SQLite2CE - Starting conversion.");
            Console.WriteLine();

            try
            {
                // check params
                CheckParams();

                // create connections
                using (SQLiteConnection liteConn = GetLiteConn(true))
                using (SqlCeConnection ceConn = GetCEConn(true))
                {
                    // check if triggers exist and bail out if we haven't been asked to ignore them
                    if (!_skipTriggers && CheckIfTriggersExist(liteConn))
                    {
                        throw new ArgumentException("The database being converted contains triggers, but SQLite2CE currently does not support conversion of triggers. " +
                            "If you would like to skip triggers and convert the rest of the database, please specify the 'skip triggers' flag and try the conversion again.");
                    }

                    // convert
                    ConvertSchema(liteConn, ceConn);

                    // verify
                    VerifySchema(liteConn, ceConn);
                    if (!_skipData)
                        VerifyData();
                }
            }
            catch (ArgumentException exc)
            {
                _sbReport.AppendLine("SQLite2CE Error: " + exc.Message);
                _sbReport.AppendLine();
            }
            catch (Exception exc)
            {
                _sbReport.AppendLine("SQLite2CE Error: " + exc.Message);
                _sbReport.AppendLine();
            }

            if (!string.IsNullOrEmpty(_reportPath))
                File.WriteAllText(_reportPath, _sbReport.ToString());
            else
                Console.Write(_sbReport.ToString());

            Console.WriteLine("SQLite2CE - Conversion completed.");
            //Console.WriteLine();
            //Console.WriteLine("Press any key to continue.");
            //Console.Read();
        }

        private SQLiteConnection GetLiteConn(bool bOpen)
        {
            string liteConnectionString = "Data Source=" + _liteDBPath;
            SQLiteConnection liteConn = new SQLiteConnection(liteConnectionString);

            if (bOpen)
                liteConn.Open();
            return liteConn;
        }

        private SqlCeConnection GetCEConn(bool bOpen)
        {
            string ceConnectionString = String.Format(CultureInfo.InvariantCulture, "Data Source='{0}';",
                _ceDBPath);

            if (!File.Exists(_ceDBPath))
            {
                SqlCeEngine ceEngine = new SqlCeEngine(ceConnectionString);
                ceEngine.CreateDatabase();
            }

            SqlCeConnection ceConn = new SqlCeConnection(ceConnectionString);
            if (bOpen)
                ceConn.Open();
            return ceConn;
        }

        private static bool CheckIfTriggersExist(SQLiteConnection liteConn)
        {
            DataTable triggers;

            try
            {
                triggers = liteConn.GetSchema("Triggers");
            }
            catch (NotSupportedException)
            {
                // ignore if not supported
                return false;
            }

            return (triggers.Rows != null) && (triggers.Rows.Count > 0);
        }

        private void ConvertSchema(SQLiteConnection liteConn, SqlCeConnection ceConn)
        {
            // Note: GetSchema param values: ReservedWords, MetaDataCollections, DataSourceInformation, DataTypes, Columns, Tables, Views, ViewColumns, Catalogs, 
            //      Indexes, IndexColumns, ForeignKeys
            // go over all the tables in original db and create them in the new db

            // remember tables we were able to add and were not able to add. This is necessary because sometimes tables that have foreign key dependencies on other tables come before
            // the tables they depend upon. So we skip those tables and handle them after the tables they depend upon have been added
            List<string> addedTableNames = new List<string>();
            List<string> skippedTableNames = new List<string>();
            int prevSkippedTableCount = 0;

            do
            {
                prevSkippedTableCount = skippedTableNames.Count();
                DataTable liteTables = liteConn.GetSchema("Tables");

                // Display the contents of the table.
                foreach (DataRow liteTable in liteTables.Rows)
                {
                    string liteTableName = (string)liteTable[2];
                    string liteTableSchema = (string)liteTable[6];

                    // we have already added this table. skip 
                    if (addedTableNames.Contains(liteTableName))
                    {
                        continue;
                    }

                    _sbLiteSchemaCommands.AppendLine(liteTableSchema);
                    _sbLiteSchemaCommands.AppendLine();

                    if (liteTableName.StartsWith("sqlite_", StringComparison.OrdinalIgnoreCase)) // skip sqlite internal tables
                        continue;

                    StringBuilder sbOut = new StringBuilder();

                    sbOut.Append("CREATE TABLE [").Append(liteTableName).Append("] (");

                    // add column and fkey definitions
                    DataTable liteColumnDescs = AddColumnDefs(liteConn, liteTableName, sbOut);

                    AddFkeyConstraints(liteConn, liteTableName, sbOut);

                    sbOut.Append(")");

                    SqlCeCommand ceCommand = ceConn.CreateCommand();
                    ceCommand.CommandText = sbOut.ToString();

                    _sbCESchemaCommands.AppendLine(sbOut.ToString());
                    _sbCESchemaCommands.AppendLine();
                    

                    try
                    {
                        ceCommand.ExecuteNonQuery();
                    }
                    catch (SqlCeException exc)
                    {
                        if ((uint)exc.HResult == 0x80040e37)
                        {
                            if (!skippedTableNames.Contains(liteTableName))
                                skippedTableNames.Add(liteTableName);
                        }
                        else
                        {
                            string msg = "SQLite2CE Error: Could not create table: " + liteTableName + "\r\nCreate table command: " +
                                ceCommand.CommandText + "\r\nReason: " + exc.Message;

                            _sbReport.AppendLine("----------------------------------------------------------------------");
                            _sbReport.AppendLine(msg);
                            _sbReport.AppendLine("----------------------------------------------------------------------");
                        }

                        // skip this table 
                        continue;
                    }

                    // convert indices
                    ConvertIndexes(liteConn, ceConn, liteTableName);

                    // convert data
                    if (!_skipData)
                        ConvertData(liteConn, ceConn, liteTableName, liteColumnDescs);

                    if (skippedTableNames.Contains(liteTableName))
                        skippedTableNames.Remove(liteTableName);

                    addedTableNames.Add(liteTableName);
                }

                if ((skippedTableNames.Count > 0) && (skippedTableNames.Count == prevSkippedTableCount)) // skipped table count did not change, looks like we are in a never ending loop
                {
                    string msg = "SQLite2CE Error: Unrecoverable errors in table schema conversion. Please correct the errors and try the conversion again.";

                    _sbReport.AppendLine("----------------------------------------------------------------------");
                    _sbReport.AppendLine(msg);
                    _sbReport.AppendLine("----------------------------------------------------------------------");
                    break;
                }

            } while (skippedTableNames.Count > 0);

            try
            {
                // Create LINQ to SQL Classes.
                LinqToSqlClassGenerator classGen = new LinqToSqlClassGenerator();
                String fileNameContainingCode = classGen.Generate(_ceDBPath, _sbReport, _wantVerboseReport);

                // convert views
                ConvertViews(liteConn, fileNameContainingCode);
            }
            catch (System.ComponentModel.Win32Exception exc)
            {
                if ((uint)exc.ErrorCode == 0x80004005)
                {
                    throw new FileNotFoundException("Cound not find SQLMetal.exe");
                }
            }
        }

        private static DataTable AddColumnDefs(SQLiteConnection liteConn, string liteTableName, StringBuilder sbOut)
        {
            // Note: GetSchema restriction values: Catalog, Schema, Table, Column
            List<string> primaryKeyList = new List<string>();
            bool usePrimaryKey = true;

            DataTable liteColumnDescs = liteConn.GetSchema("COLUMNS", new string[] { null, null, liteTableName, null });
            for (int iColumnDesc = 0; iColumnDesc < liteColumnDescs.Rows.Count; iColumnDesc++)
            {
                DataRow liteColumnDesc = liteColumnDescs.Rows[iColumnDesc];
                string colName = liteColumnDesc["COLUMN_NAME"].ToString();

                if (iColumnDesc > 0)
                    sbOut.Append(", ");

                sbOut.Append("[").Append(colName).Append("]").Append(" ");

                string liteDataType = liteColumnDesc["DATA_TYPE"].ToString().ToLowerInvariant();
                int liteMaxLength = (int)liteColumnDesc["CHARACTER_MAXIMUM_LENGTH"];
                string ceDataType;
                int ceMaxLength;
                CEDataTypeFromLite(liteDataType, liteMaxLength, out ceDataType, out ceMaxLength);

                sbOut.Append(ceDataType);

                if (((ceDataType == "nchar") || (ceDataType == "nvarchar")) && (ceMaxLength > 0))
                {
                    if (ceMaxLength == int.MaxValue)
                        sbOut.Append(" (" + _settings.MaxNCharLength + ") "); // if max length is unspecified, use reasonable max value
                    else
                        sbOut.Append(" (").Append(ceMaxLength.ToString(CultureInfo.InvariantCulture)).Append(") ");
                }

                if ((bool)liteColumnDesc["AUTOINCREMENT"])
                    sbOut.Append(" IDENTITY");

                //if ((bool)liteColumnDesc["PRIMARY_KEY"])
                //    sbOut.Append(" PRIMARY KEY");
                if ((bool)liteColumnDesc["PRIMARY_KEY"])
                {
                    if (IsPrimaryKeyAllowed(ceDataType))
                        primaryKeyList.Add(colName);
                    else
                        usePrimaryKey = false;

                }

                else if ((bool)liteColumnDesc["UNIQUE"])
                    sbOut.Append(" UNIQUE");

                if ((bool)liteColumnDesc["IS_NULLABLE"])
                    sbOut.Append(" NULL");
                else
                    sbOut.Append(" NOT NULL");

                // Handle default value
                if ((bool)liteColumnDesc["COLUMN_HASDEFAULT"] && !String.IsNullOrEmpty(liteColumnDesc["COLUMN_DEFAULT"].ToString()))
                {
                    string liteDefaultValue = liteColumnDesc["COLUMN_DEFAULT"].ToString().Replace("((", "").Replace("))", "");
                    string ceDefaultValue = CEValueFromLite(liteDataType, liteDefaultValue);
                    sbOut.Append(" DEFAULT (").Append(ceDefaultValue).Append(")");
                }
            }
            if (usePrimaryKey && primaryKeyList.Count > 0)
            {
                sbOut.Append(", ");
                sbOut.Append(" PRIMARY KEY (");
                for (int i = 0; i < primaryKeyList.Count; i++)
                {
                    sbOut.Append(primaryKeyList.ElementAt(i));
                    if (i != primaryKeyList.Count-1)
                        sbOut.Append(", ");
                }
                sbOut.Append(" )");

            }

            return liteColumnDescs;
        }

        private void ConvertIndexes(SQLiteConnection liteConn, SqlCeConnection ceConn, string liteTableName)
        {
            DataTable liteIndexColumnDescs = liteConn.GetSchema("IndexColumns", new string[] { null, null, liteTableName, null });
            Dictionary<string, Dictionary<string, List<string>>> indexDict = new Dictionary<string, Dictionary<string, List<string>>>();

            foreach (System.Data.DataRow liteIndexColumnDesc in liteIndexColumnDescs.Rows)
            {
                string indexName = (string)liteIndexColumnDesc["INDEX_NAME"];

                // UNIQUE columns are implemented using sqlite_autoindex
                if (indexName.StartsWith("sqlite_master_PK_", StringComparison.OrdinalIgnoreCase) ||
                    indexName.StartsWith("sqlite_autoindex_", StringComparison.OrdinalIgnoreCase)) // skip indexes that are automatically created for primary key and UNIQUE columns
                    continue;

                Dictionary<string, List<string>> detailsDict;
                if (!indexDict.ContainsKey(indexName))
                {
                    detailsDict = new Dictionary<string, List<string>>();
                    detailsDict.Add("SORT_MODE", new List<string>());
                    detailsDict.Add("COLUMN_NAME", new List<string>());
                    detailsDict.Add("CONFLICT_OPTION", new List<string>());
                    indexDict.Add(indexName, detailsDict);
                }

                detailsDict = indexDict[indexName];
                detailsDict["COLUMN_NAME"].Add((string)liteIndexColumnDesc["COLUMN_NAME"]);

                if (!string.IsNullOrEmpty((string)liteIndexColumnDesc["SORT_MODE"]))
                    detailsDict["SORT_MODE"].Add((string)liteIndexColumnDesc["SORT_MODE"]);
                else
                    detailsDict["SORT_MODE"].Add("");

                if ((int)liteIndexColumnDesc["CONFLICT_OPTION"] == 2) // experimentally obtained value, haven't found docs on this
                    detailsDict["CONFLICT_OPTION"].Add("UNIQUE");
                else
                    detailsDict["CONFLICT_OPTION"].Add("");
            }

            foreach (string indexName in indexDict.Keys)
            {
                StringBuilder sbOut = new StringBuilder();
                sbOut.Append("CREATE");

                foreach (string columnUniqueType in indexDict[indexName]["CONFLICT_OPTION"])
                {
                    if (columnUniqueType == "UNIQUE")
                    {
                        sbOut.Append(" UNIQUE ");
                        break;
                    }
                }

                sbOut.Append(" INDEX ").Append(indexName).Append(" ON ").Append(liteTableName).Append("(");

                int i = 0;
                foreach (string columnName in indexDict[indexName]["COLUMN_NAME"])
                {
                    sbOut.Append(columnName);

                    if (!string.IsNullOrEmpty((string)indexDict[indexName]["SORT_MODE"][i]))
                        sbOut.Append(" ").Append((string)indexDict[indexName]["SORT_MODE"][i]);

                    if (i != indexDict[indexName]["COLUMN_NAME"].Count - 1)
                        sbOut.Append(",");

                    i++;
                }

                sbOut.Append(")");

                SqlCeCommand ceCommand = ceConn.CreateCommand();
                ceCommand.CommandText = sbOut.ToString();

               
               _sbCESchemaCommands.AppendLine(sbOut.ToString());
               _sbCESchemaCommands.AppendLine();
               

                try
                {
                    ceCommand.ExecuteNonQuery();
                }
                catch (SqlCeException exc)
                {
                    string msg = "SQLite2CE Warning: Could not create index for table: " + liteTableName + ", Index name: " + indexName + "\r\nCreate index command: " +
                        ceCommand.CommandText + "\r\nReason: ";

                    if (exc.Message.StartsWith("long value data type cannot be indexed", StringComparison.OrdinalIgnoreCase))
                        msg += "SQL Server CE does not allow indexes on columns of type ntext or image. The index will not be converted.";
                    else
                        msg += exc.Message;

                    _sbReport.AppendLine("----------------------------------------------------------------------");
                    _sbReport.AppendLine(msg);
                    _sbReport.AppendLine("----------------------------------------------------------------------");
                }
            }
        }

        private static void AddFkeyConstraints(SQLiteConnection liteConn, string liteTableName, StringBuilder sbOut)
        {
            DataTable fkeyDescs = liteConn.GetSchema("ForeignKeys", new string[] { null, null, liteTableName, null });

            foreach (System.Data.DataRow liteFkeyDesc in fkeyDescs.Rows)
            {
                sbOut.Append(", FOREIGN KEY ([");
                sbOut.Append((string)liteFkeyDesc["FKEY_FROM_COLUMN"]);
                sbOut.Append("]) REFERENCES [");
                sbOut.Append((string)liteFkeyDesc["FKEY_TO_TABLE"]);
                sbOut.Append("] ([");
                sbOut.Append((string)liteFkeyDesc["FKEY_TO_COLUMN"]);
                sbOut.Append("])");
            }
        }

        private void ConvertViews(SQLiteConnection liteConn, string fileNameContainingSqlMetalCode)
        {
            if (_skipViews)
                return;

            List<ViewInfo> views;
            String outputDirectoryLoc;
            ViewCodeGenCtrlInput inputs;
            ViewInfo currentViewInfo;
            ViewCodeGeneratorController viewCodeGenCtrl;
            DataTable listOfViews = liteConn.GetSchema("Views");

            outputDirectoryLoc = new FileInfo(_ceDBPath).Directory.FullName;

            views = new List<ViewInfo>();
            viewCodeGenCtrl = new ViewCodeGeneratorController();
            inputs = new ViewCodeGenCtrlInput()
            {
                LocationOfSqlMetalClasses = Path.Combine(outputDirectoryLoc, fileNameContainingSqlMetalCode),
                DirectoryForViewCsFile = outputDirectoryLoc
            };

            foreach (DataRow viewInfo in listOfViews.Rows)
            {
                currentViewInfo = new ViewInfo()
                {
                    ViewName = viewInfo["TABLE_NAME"].ToString(),
                    ViewQuery = viewInfo["VIEW_DEFINITION"].ToString()
                };

                views.Add(currentViewInfo);
            }

            inputs.Views = views;
            viewCodeGenCtrl.GenerateClassForView(inputs);
        }

        private void ConvertData(SQLiteConnection liteConn, SqlCeConnection ceConn, string tableName, DataTable liteColumnDescs)
        {
            // create CE command
            using (SqlCeCommand ceCommand = new SqlCeCommand())
            {
                ceCommand.Connection = ceConn;
                StringBuilder sbCECommandText = new StringBuilder();

                SetIdentityInsert(ceConn, tableName, true);

                // prepare command text format for creating individual insert commands
                sbCECommandText.Append("INSERT INTO [" + tableName + "] (");

                // add columns names
                for (int iCol = 0; iCol < liteColumnDescs.Rows.Count; iCol++)
                {
                    DataRow liteColumnDesc = liteColumnDescs.Rows[iCol];
                    string colName = liteColumnDesc["COLUMN_NAME"].ToString();

                    sbCECommandText.Append("[").Append(colName);
                    sbCECommandText.Append("],");
                }

                // remove last comma
                if (sbCECommandText[sbCECommandText.Length - 1] == ',')
                    sbCECommandText.Remove(sbCECommandText.Length - 1, 1);

                sbCECommandText.Append(") VALUES (");
                string ceCommandText = sbCECommandText.ToString();

                // read from lite db
                using (SQLiteCommand liteCommand = new SQLiteCommand("select * from [" + tableName + "]", liteConn))
                using (SQLiteDataReader reader = liteCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        object[] values = new object[reader.FieldCount];
                        reader.GetValues(values);

                        ceCommand.Parameters.Clear();
                        sbCECommandText.Clear();
                        sbCECommandText.Append(ceCommandText);

                        for (int iCol = 0; iCol < values.Length; iCol++)
                        {
                            DataRow liteColumnDesc = liteColumnDescs.Rows[iCol];
                            object value = values[iCol];
                            string liteType = reader.GetDataTypeName(iCol).ToLowerInvariant();
                            string columnName = reader.GetName(iCol);

                            if (reader.IsDBNull(iCol))
                                sbCECommandText.Append("NULL");
                            else if (liteType.StartsWith("text", StringComparison.OrdinalIgnoreCase) ||
                                liteType.StartsWith("ntext", StringComparison.OrdinalIgnoreCase) ||
                                liteType.StartsWith("varchar", StringComparison.OrdinalIgnoreCase) ||
                                liteType.StartsWith("nvarchar", StringComparison.OrdinalIgnoreCase) ||
                                liteType.StartsWith("char", StringComparison.OrdinalIgnoreCase) ||
                                liteType.StartsWith("nchar", StringComparison.OrdinalIgnoreCase))
                            {
                                sbCECommandText.Append("'" + value.ToString().Replace("'", "''") + "'");
                            }
                            else if (liteType.StartsWith("date", StringComparison.OrdinalIgnoreCase) ||
                                     liteType.StartsWith("datetime", StringComparison.OrdinalIgnoreCase) == true)
                            {
                                DateTime dateTimeValue = (DateTime)value;
                                DateTime correctedDateTimeValue = dateTimeValue < SqlCEMinDateTime ? SqlCEMinDateTime : dateTimeValue;
                                sbCECommandText.Append("'" + correctedDateTimeValue.ToString("MM-dd-yyyy hh:mm:ss tt") + "'");
                            }
                            else if ((liteType == "bit") || (liteType == "boolean") || (liteType == "bool"))
                                sbCECommandText.Append((bool)value ? 1 : 0);
                            else if (liteType == "string")
                            {
                                sbCECommandText.Append("@" + columnName);
                                byte[] convertedVal = System.Text.UTF8Encoding.UTF8.GetBytes(value.ToString());
                                ceCommand.Parameters.Add("@" + columnName, SqlDbType.Binary, convertedVal.Length).Value = convertedVal;
                            }
                            else if (liteType == "blob" || liteType == "image" || liteType == "clob" || liteType == "varbinary" || liteType == "byte[]")
                            {
                                sbCECommandText.Append("@" + columnName);
                                byte[] abValue = (byte[])value;
                                ceCommand.Parameters.Add("@" + columnName, SqlDbType.Binary, (abValue).Length).Value = abValue;
                            }
                            else if (liteType == "real" || liteType == "float" || liteType == "double")
                            {
                                sbCECommandText.Append(System.Convert.ToString(value, CultureInfo.InvariantCulture));
                            }
                            else
                                sbCECommandText.Append(value);

                            sbCECommandText.Append(",");
                        }

                        // remove last comma
                        if (sbCECommandText[sbCECommandText.Length - 1] == ',')
                            sbCECommandText.Remove(sbCECommandText.Length - 1, 1);

                        sbCECommandText.Append(")");
                        ceCommand.CommandText = sbCECommandText.ToString();

                        
                        _sbCEDataCommands.AppendLine(sbCECommandText.ToString());
                        _sbCEDataCommands.AppendLine();
                        

                        try
                        {
                            ceCommand.ExecuteNonQuery();
                        }
                        catch (SqlCeException exc)
                        {
                            string msg = "SQLite2CE Error: Could not insert data into table: " + tableName + "\r\nInsert command: " +
                                ceCommand.CommandText + "\r\nReason: " + exc.Message;

                            _sbReport.AppendLine("----------------------------------------------------------------------");
                            _sbReport.AppendLine(msg);
                            _sbReport.AppendLine("----------------------------------------------------------------------");
                        }
                    }
                }

                SetIdentityInsert(ceConn, tableName, false);
            }
        }

        private static void SetIdentityInsert(SqlCeConnection ceConn, string tableName, bool isOn)
        {
            using (SqlCeCommand ceCommand = ceConn.CreateCommand())
            {
                ceCommand.CommandText = "SET IDENTITY_INSERT [" + tableName + (isOn ? "] ON" : "] OFF");

                try
                {
                    ceCommand.ExecuteNonQuery();
                }
                catch (SqlCeException)
                {
                    // ignore: This means table did not have an identity column, which is ok.
                }
            }
        }

        private static bool IsPrimaryKeyAllowed(string ceDataType)
        {
            switch (ceDataType)
            {
                default:
                    return true;
                case "ntext":
                case "image":
                    return false;
            }
        }

        private static void CEDataTypeFromLite(string liteDataType, int liteMaxLength, out string ceDataType, out int ceMaxLength)
        {
            ceDataType = liteDataType;
            ceMaxLength = liteMaxLength;

            switch (liteDataType)
            {
                case "bool":
                case "boolean":
                case "bit":
                    ceDataType = "bit";
                    break;

                case "real":
                case "double":
                case "float":
                    ceDataType = "float";
                    break;

                case "char":
                case "nchar":
                    if ((liteMaxLength <= 4000) || (liteMaxLength == int.MaxValue))
                        ceDataType = "nchar";
                    else
                        ceDataType = "ntext";
                    break;

                case "nvarchar":
                case "varchar":
                    if ((liteMaxLength <= 4000) || (liteMaxLength == int.MaxValue))
                        ceDataType = "nvarchar";
                    else
                        ceDataType = "ntext";
                    break;

                case "text":
                case "ntext":
                case "clob":
                    ceDataType = "ntext";
                    break;
                case "mediumtext":
                case "blob":
                    ceDataType = "image";
                    break;

                case "numeric":
                case "decimal":
                    ceDataType = "numeric";
                    break;

                case "datetime":
                case "date":
                    ceDataType = "datetime";
                    break;

                case "int":
                case "integer":
                case "smallint":
                case "tinyint":
                case "bigint":
                    ceDataType = _settings.IntegerTypeName;
                    break;

                case "money":
                    ceDataType = "money";
                    break;

                case "image":
                case "varbinary":
                    ceDataType = "image";
                    break;

                // to handle dynamic data types
                default:
                    ceDataType = _settings.DefaultDataTypeName;
                    break;
            }
        }

        private static string CEValueFromLite(string liteDataType, string liteDefaultValue)
        {
            string checkValue = liteDefaultValue.Trim().ToUpperInvariant();

            if (checkValue == "CURRENT_DATE")
                return "GETDATE()";

            if (checkValue == "CURRENT_TIME")
                return "GETDATE()";

            if (checkValue == "CURRENT_TIMESTAMP")
                return "GETDATE()";

            if (checkValue == "FALSE")
                return "0";

            if (checkValue == "TRUE")
                return "1";

            if (liteDataType == "char")
                return QuoteIfNotQuoted(liteDefaultValue);

            return liteDefaultValue;
        }

        private static string QuoteIfNotQuoted(string liteValue)
        {
            int index = liteValue.IndexOfAny(new char[] { '\'', '"' });
            int lastIndex = liteValue.LastIndexOfAny(new char[] { '\'', '"' });
            if (index != -1 && lastIndex != -1 && index != lastIndex)
                return liteValue;
            if (index != -1 && lastIndex != -1 && index == lastIndex)
                return "'" + liteValue + "'";
            if (index == -1 && lastIndex == -1)
                return "'" + liteValue + "'";
            return liteValue;
        }

        private void CheckParams()
        {
            if (!File.Exists(_liteDBPath))
            {
                throw new ArgumentException("Input database file does not exist: " + _liteDBPath);
            }

            if (File.Exists(_ceDBPath))
            {
                if (_forceOverwrite)
                    File.Delete(_ceDBPath);
                else
                    throw new ArgumentException("Output database file already exists: " + _ceDBPath);
            }

            // read settings if specified
            if (!string.IsNullOrEmpty(_settingsPath))
            {
                if (!File.Exists(_settingsPath))
                    throw new ArgumentException("Settings file does not exist: " + _settingsPath);

                try
                {
                    XmlDocument settingsDoc = new XmlDocument();

                    settingsDoc.Load(_settingsPath);

                    _settings.MaxNCharLength = int.Parse(settingsDoc.SelectSingleNode("converterSettings/maxNCharLength").InnerText, CultureInfo.InvariantCulture);
                    _settings.IntegerTypeName = settingsDoc.SelectSingleNode("converterSettings/integerTypeName").InnerText;
                    _settings.DefaultDataTypeName = settingsDoc.SelectSingleNode("converterSettings/defaultDataTypeName").InnerText;
                }
                catch (XmlException)
                {
                    throw new ArgumentException("Converter settings file could not be parsed: " + _settingsPath);
                }
                catch (NullReferenceException)
                {
                    throw new ArgumentException("Converter settings file could not be parsed: " + _settingsPath);
                }
            }
        }

        private void VerifySchema(SQLiteConnection liteConn, SqlCeConnection ceConn)
        {
            if (_wantVerboseReport)
            {
                _sbReport.AppendLine();
                _sbReport.AppendLine("------------ SQLite Database Schema ----------------------------------");

                _sbReport.Append(_sbLiteSchemaCommands.ToString());
                _sbReport.AppendLine("----------------------------------------------------------------------");
                PrintDBSchema(liteConn, false);
                _sbReport.AppendLine();

                _sbReport.AppendLine("------------ SQL CE Database Schema ----------------------------------");

                _sbReport.Append(_sbCESchemaCommands.ToString());
                _sbReport.AppendLine("----------------------------------------------------------------------");
                PrintDBSchema(ceConn, true);
                _sbReport.AppendLine();
            }
        }

        private void VerifyData()
        {
            if (_wantVerboseReport)
            {
                _sbReport.AppendLine("------------ SQL CE Data ---------------------------------------------");

                _sbReport.Append(_sbCEDataCommands.ToString());
                _sbReport.AppendLine("----------------------------------------------------------------------");
                _sbReport.AppendLine();
            }
        }

        private void PrintDBSchema(DbConnection dbConn, bool isCEConn)
        {
            // tables
            PrintTableSchemas(dbConn);
            _sbReport.AppendLine("----------------------------------------------------------------------");

            // indexes
            PrintIndexes(dbConn);
            _sbReport.AppendLine("----------------------------------------------------------------------");

            // foreign keys
            PrintForeignKeys(dbConn);
            _sbReport.AppendLine("----------------------------------------------------------------------");

            if (!isCEConn) // SQL CE does not support views and triggers
            {
                // print views
                PrintViews(dbConn);
                _sbReport.AppendLine("----------------------------------------------------------------------");

                // triggers
                PrintTriggers(dbConn);
                _sbReport.AppendLine("----------------------------------------------------------------------");
            }
        }

        private void PrintTableSchemas(DbConnection dbConn)
        {
            DataTable tables;

            try
            {
                tables = dbConn.GetSchema("Tables");
            }
            catch (NotSupportedException)
            {
                // ignore if not supported
                return;
            }

            _sbReport.AppendLine("All Tables:");
            _sbReport.AppendLine("----------------------------------------------------------------------");

            // Display the contents of the table.
            foreach (System.Data.DataRow row in tables.Rows)
            {
                string tableName = (string)row[2];

                _sbReport.AppendLine("Table name: " + tableName);

                try
                {
                    _sbReport.AppendLine("Table definition: " + (string)row["TABLE_DEFINITION"]);
                }
                catch (ArgumentException)
                {
                    // ignore if not supported
                }

                _sbReport.AppendLine("----------------------------------------------------------------------");

                //foreach (System.Data.DataColumn col in table.Columns)
                //{
                //    _sbReport.AppendLine("{0} = {1}", col.ColumnName, row[col]);
                //}

                //_sbReport.AppendLine("----------------------------------------------------------------------");
                //_sbReport.AppendLine("Columns:");

                DataTable colsTable = dbConn.GetSchema("COLUMNS", new string[] { null, null, tableName, null });
                foreach (System.Data.DataRow colRow in colsTable.Rows)
                {
                    for (int i = 0; i < colRow.ItemArray.Length; i++)
                    {
                        object item = colRow.ItemArray[i];
                        _sbReport.AppendFormat("{0} = {1}", colsTable.Columns[i].ColumnName, item.ToString());
                        _sbReport.AppendLine();
                    }

                    _sbReport.AppendLine("----------------------------------------------------------------------");
                }

                _sbReport.AppendLine("----------------------------------------------------------------------");
                _sbReport.AppendLine();
            }
        }

        private void PrintIndexes(DbConnection dbConn)
        {
            DataTable indexColDescs;

            try
            {
                indexColDescs = dbConn.GetSchema("IndexColumns", new string[] { null, null, null, null });
            }
            catch (NotSupportedException)
            {
                // ignore if not supported
                return;
            }

            _sbReport.AppendLine("All Indexes:");

            foreach (System.Data.DataRow indexColDesc in indexColDescs.Rows)
            {
                for (int i = 0; i < indexColDesc.ItemArray.Length; i++)
                {
                    object item = indexColDesc.ItemArray[i];
                    _sbReport.AppendFormat("{0} = {1}", indexColDescs.Columns[i].ColumnName, item.ToString());
                    _sbReport.AppendLine();
                }

                _sbReport.AppendLine("----------------------------------------------------------------------");
                _sbReport.AppendLine();
            }
        }

        private void PrintForeignKeys(DbConnection dbConn)
        {
            DataTable fkeyDescs;

            try
            {
                fkeyDescs = dbConn.GetSchema("ForeignKeys", new string[] { null, null, null, null });
            }
            catch (NotSupportedException)
            {
                // ignore if not supported
                return;
            }

            _sbReport.AppendLine("All Foreign Keys:");

            foreach (System.Data.DataRow fkeyDesc in fkeyDescs.Rows)
            {
                for (int i = 0; i < fkeyDesc.ItemArray.Length; i++)
                {
                    object item = fkeyDesc.ItemArray[i];
                    _sbReport.AppendFormat("{0} = {1}", fkeyDescs.Columns[i].ColumnName, item.ToString());
                    _sbReport.AppendLine();
                }

                _sbReport.AppendLine("----------------------------------------------------------------------");
                _sbReport.AppendLine();
            }
        }

        private void PrintViews(DbConnection dbConn)
        {
            DataTable views;

            try
            {
                views = dbConn.GetSchema("Views");
            }
            catch (NotSupportedException)
            {
                // ignore if not supported
                return;
            }

            _sbReport.AppendLine("All Views:");
            _sbReport.AppendLine("----------------------------------------------------------------------");

            // Display the contents of the table.
            foreach (System.Data.DataRow row in views.Rows)
            {
                string viewName = (string)row[2];

                _sbReport.AppendLine("View name: " + viewName);

                try
                {
                    _sbReport.AppendLine("View query: " + (string)row["VIEW_DEFINITION"]);
                }
                catch (ArgumentException)
                {
                    // ignore if not supported
                }

                _sbReport.AppendLine("----------------------------------------------------------------------");

                //foreach (System.Data.DataColumn col in table.Columns)
                //{
                //    _sbReport.AppendLine("{0} = {1}", col.ColumnName, row[col]);
                //}

                //_sbReport.AppendLine("----------------------------------------------------------------------");
                //_sbReport.AppendLine("Columns:");

                DataTable colsView = dbConn.GetSchema("ViewColumns", new string[] { null, null, viewName, null });
                foreach (System.Data.DataRow colRow in colsView.Rows)
                {
                    for (int i = 0; i < colRow.ItemArray.Length; i++)
                    {
                        object item = colRow.ItemArray[i];
                        _sbReport.AppendFormat("{0} = {1}", colsView.Columns[i].ColumnName, item.ToString());
                        _sbReport.AppendLine();
                    }

                    _sbReport.AppendLine("----------------------------------------------------------------------");
                }

                _sbReport.AppendLine("----------------------------------------------------------------------");
                _sbReport.AppendLine();
            }
        }

        private void PrintTriggers(DbConnection dbConn)
        {
            DataTable triggers;

            try
            {
                triggers = dbConn.GetSchema("Triggers");
            }
            catch (NotSupportedException)
            {
                // ignore if not supported
                return;
            }

            _sbReport.AppendLine("All Triggers:");
            _sbReport.AppendLine("----------------------------------------------------------------------");

            // Display the contents of the table.
            foreach (System.Data.DataRow row in triggers.Rows)
            {
                string triggerName = (string)row[2];

                _sbReport.AppendLine("Trigger name: " + triggerName);

                try
                {
                    _sbReport.AppendLine("Trigger definition: " + (string)row["TRIGGER_DEFINITION"]);
                }
                catch (ArgumentException)
                {
                    // ignore if not supported
                }

                _sbReport.AppendLine("----------------------------------------------------------------------");
                _sbReport.AppendLine();
            }
        }
    }

    public class ConverterSettings
    {
        public int MaxNCharLength { get; set; }
        public string IntegerTypeName { get; set; }
        public string DefaultDataTypeName { get; set; }

        public ConverterSettings()
        {
            MaxNCharLength = 500;
            IntegerTypeName = "bigint";
            DefaultDataTypeName = "image";
        }
    }

}
